<?php
/**
 * Created by PhpStorm.
 * User: ran
 * Date: 2020/8/13
 * Time: 11:25
 */

namespace App\Statistics\Service;


use App\Order\Model\OrderModel;
use App\Order\Model\ShopOrderModel;
use App\Resource\Model\GoodsModel;
use App\Resource\Model\ShopModel;
use App\User\Model\MemberModel;
use Hyperf\Database\Model\Builder;
use Hyperf\Database\Model\Model;
use App\Common\Service\BaseService;
use Hyperf\DbConnection\Db;

class HomeService extends BaseService
{
    /**
     * 获取首页实时门店销售数据
     * @param array $where
     * @param array|string[] $field
     * @return array|Builder|Model|object
     */
    public function getHomeShopData(array $where = [], array $field = ['*'])
    {
        $data = ShopOrderModel::query()->where($where)->where(['time' => date('Y-m-d', time())])->select($field)->get()->toArray();
        $shopListByShopId = self::getShopInfo($data);
        foreach ($data as $key => $vo) {
            $data[$key]['shop_name'] = $shopListByShopId[$vo['shop_id']]['shop_name'] ?? '未找到对应店铺';
        }
        return $data;
    }

    /**
     * 首页数据服务
     * @return array
     */
    public function platformHomeStatistics()
    {
        //今日退款额
        $platformTodayData = $this->getTodayHomeData();
        $platformYesterdayData = $this->getYesterdayHomeData();
        $platformRedisKey = 'homeAnalyse:' . date('Ymd', time());
        $platform_shop_user_count = !empty($this->redis->get($platformRedisKey)) ? $this->redis->get($platformRedisKey) : 0;
        $platformData = [
            'platform_total_money' => [
                'platform_today_total_money' => $platformTodayData['recharge'] ?? 0,
                'platform_yesterday_total_money' => $platformYesterdayData['recharge'] ?? 0,
                'percentage' => bcmul(@bcdiv(bcsub($platformTodayData['recharge'], $platformYesterdayData['recharge'], 2), $platformYesterdayData['recharge'], 2), 100) . '%',
            ],
            'platform_refund_total_money' => [
                'platform_today_total_money' => $platformTodayData['total_refund_money'] ?? 0,
                'platform_yesterday_total_money' => $platformYesterdayData['total_refund_money'] ?? 0,
                'percentage' => bcmul(@bcdiv(bcsub($platformTodayData['total_refund_money'], $platformYesterdayData['total_refund_money'], 2), $platformYesterdayData['total_refund_money'], 2), 100) . '%',
            ],
            'platform_pay_order_count' => [
                'platform_today_order_count' => $platformTodayData['recharge_order_count'] ?? 0,
                'platform_yesterday_order_count' => $platformYesterdayData['recharge_order_count'] ?? 0,
                'percentage' => bcmul(@bcdiv(bcsub($platformTodayData['recharge_order_count'], $platformYesterdayData['recharge_order_count'], 2), $platformYesterdayData['recharge_order_count'], 2), 100) . '%',
            ],
            'platform_goods_sale_num' => [
                'platform_today_goods_sale_num' => $platformTodayData['total_goods_sale_num'] ?? 0,
                'platform_yesterday_goods_sale_num' => $platformYesterdayData['total_goods_sale_num'] ?? 0,
                'percentage' => bcmul(@bcdiv(bcsub($platformTodayData['total_goods_sale_num'], $platformYesterdayData['total_goods_sale_num'], 2), $platformYesterdayData['total_goods_sale_num'], 2), 100) . '%',
            ],
            'platform_user' => [
                'platform_today_platform_user' => $this->todayAddMemberNum() ?? 0,
                'platform_yesterday_platform_user' => $this->yesterdayAddMemberNum() ?? 0,
                'percentage' => bcmul(@bcdiv(bcsub($this->todayAddMemberNum(), $this->yesterdayAddMemberNum()), $this->yesterdayAddMemberNum()), 100) . '%',
            ],
            'platform_goods' => [
                'platform_shelves_goods' => $this->getShelvesGoods() ?? 0,
                'platform_shelves_loop_goods' => $this->getShelvesLoopGoods() ?? 0,
                'platform_total_goods' => $this->getTotalGoods(),
            ],
            'platform_shop_user_count' => $platform_shop_user_count,
            'platform_total_pay_user_count' => $platformTodayData['total_pay_user_count'] ?? 0,
            'platform_plc_conversion_rate' => 10,
            'platform_real_total_money' => bcsub($platformTodayData['recharge'], $platformTodayData['total_refund_money'], 2) ?? 0,
            'platform_tools_convert_price' => @bcdiv($platformTodayData['recharge'], $platformTodayData['total_pay_user_count'], 2) ?? 0,
            'platform_arppu' => $this->getMemberARPPU(),
            'platform_followed_users' => $this->getLossMemberOrder()->count(),
            'platform_total_users' => $this->getTotalMember(),
            'platform_users_per_roster' => round($this->getLossMemberOrder()->avg('total_price'),2),
            'platform_month_add_member_num' => $this->MonthAddMemberNum() ?? 0,
            'platform_update_date' => $platformTodayData['update_date'],
            'platform_add_pay_member_num' => $this->getTodayNewPayMemberNumber(),
        ];
        return $platformData;
    }

    /**
     * @return array
     */
    public function getTodayHomeData()
    {
        $platformTodayData = ShopOrderModel::query()->where(['time' => date('Y-m-d', time())])->selectRaw('SUM(wx_refund_recharge+kz_refund_recharge) as total_refund_money,SUM(recharge) as recharge,SUM(recharge_order_count) as recharge_order_count,SUM(total_goods_sale_num) as total_goods_sale_num,SUM(total_pay_user_count) as total_pay_user_count,update_date')->first()->toArray();
        return $platformTodayData;
    }

    /**
     * @return array
     */
    public function getYesterdayHomeData()
    {
        $platformYesterdayData = ShopOrderModel::query()->where(['time' => date('Y-m-d', strtotime("-1 day"))])->selectRaw('SUM(wx_refund_recharge+kz_refund_recharge) as total_refund_money,SUM(recharge) as recharge,SUM(recharge_order_count) as recharge_order_count,SUM(total_goods_sale_num) as total_goods_sale_num')->first()->toArray();

        return $platformYesterdayData;
    }

    /**
     * 当天注册会员数
     * @return string
     */
    public function getTotalMember()
    {
        return MemberModel::count();
    }

    /**
     * 本月新增会员数
     * @return string
     */
    public function MonthAddMemberNum()
    {
        return MemberModel::whereMonth('register_date', date('m', time()))->count();
    }

    /**
     * 当天注册会员数
     * @return string
     */
    public function todayAddMemberNum()
    {
        return MemberModel::whereDate('register_date', date('Y-m-d', time()))->count();
    }

    /**
     * 昨日注册会员数
     * @return string
     */
    public function yesterdayAddMemberNum()
    {
        return MemberModel::whereDate('register_date', date('Y-m-d', strtotime("-1 day")))->count();
    }

    /**
     * 下架商品
     * @return string
     */
    public function getShelvesGoods()
    {
        return GoodsModel::where(['status' => 0])->count();
    }

    /**
     * 下架商品
     * @return string
     */
    public function getShelvesLoopGoods()
    {
        return GoodsModel::where(['status' => 1])->count();
    }

    /**
     * 下架商品
     * @return string
     */
    public function getTotalGoods()
    {
        return GoodsModel::count();
    }

    /**
     * 获取店铺信息
     * @param $shopData
     * @return array
     */
    public static function getShopInfo(&$shopData)
    {
        $shopIdsArr = array_unique(array_column($shopData, 'shop_id'));
        $shopList = ShopModel::whereIn('shop_id', $shopIdsArr)->select(['shop_id', 'shop_name', 'shop_tel', 'shop_desc', 'shop_group_id'])->get()->toArray();
        $shopListByShopId = array_column($shopList, null, 'shop_id');
        return $shopListByShopId;
    }

    /**
     * 获取流失用户订单
     *
     * @return Builder[]|\Hyperf\Database\Model\Collection|\Hyperf\Database\Query\Builder[]|\Hyperf\Utils\Collection
     */
    protected function getLossMemberOrder(){
        return OrderModel::query()
                  ->select('mid','total_price')
                  ->where('is_pay', 1)
                  ->where('pay_at', '<', date('Y-m-d', strtotime('-60 day')))
                  ->whereNotExists(function ($query) {
                      $query->select('mid')
                            ->from('store_order as lately')
                            ->where('is_pay', 1)
                            ->whereRaw('store_order.mid = lately.mid')
                            ->where('pay_at', '>', date('Y-m-d', strtotime('-60 day')))
                            ->groupBy('mid');
                  })
                  ->groupBy('mid')
                  ->get();
    }

    /**
     * 获取 ARPPU
     *
     * @return false|float|int
     */
    public function getMemberARPPU()
    {
        $res = OrderModel::selectRaw('sum(total_price) as total_price')
                         ->where('is_pay', 1)
                         ->where('pay_at', '>', date('Y-m-d', strtotime('-30 day')))
                         ->groupBy('mid')
                         ->get()
                         ->avg('total_price');
        return round($res, 2) ?? 0;
    }


    /**
     * 获取今日新增付费用户数量
     *
     * @return int
     */
    public function getTodayNewPayMemberNumber(): int
    {
        return MemberModel::leftJoin('store_order as order', 'order.mid', '=', 'store_member.mid')
                          ->whereRaw("to_days(store_member.register_date) = to_days(now())")
                          ->where('is_pay', 1)
                          ->groupBy('order.mid')
                          ->count() ?? 0;
    }

}
